1 Imports System.Data.SqlClient
2 Public Class frmDepartment
3
4 Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
5 Me.Close()
6 End Sub
7 Private Sub auto()
8 Try
9 Dim Num As Integer = 0
10 con = New SqlConnection(cs)
11 con.Open()
12 Dim sql As String = ("SELECT MAX(ID) FROM Department")
13 cmd = New SqlCommand(sql)
14 cmd.Connection = con
15 If (IsDBNull(cmd.ExecuteScalar)) Then
16 Num = 1
17 txtID.Text = Num.ToString
18 Else
19 Num = cmd.ExecuteScalar + 1
20 txtID.Text = Num.ToString
21 End If
22 cmd.Dispose()
23 con.Close()
24 con.Dispose()
25 Catch ex As Exception
26 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
27 End Try
28 End Sub
29 Sub Reset()
30 txtDepartment.Text = ""
31 btnSave.Enabled = True
32 btnDelete.Enabled = False
33 btnUpdate.Enabled = False
34 txtDepartment.Focus()
35 auto()
36 End Sub
37 Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNew.Click
38 Reset()
39 End Sub
40
41 Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
42 If txtDepartment.Text = "" Then
43 MessageBox.Show("Please enter Department", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
44 txtDepartment.Focus()
45 Return
46 End If
47
48 Try
49 con = New SqlConnection(cs)
50 con.Open()
51 Dim ct As String = "select DepartmentName from Department where DepartmentName=@d1"
52 cmd = New SqlCommand(ct)
53 cmd.Parameters.AddWithValue("@d1", txtDepartment.Text)
54 cmd.Connection = con
55 rdr = cmd.ExecuteReader()
56
57 If rdr.Read() Then
58 MessageBox.Show("Department Already Exists", "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
59 txtDepartment.Text = ""
60 txtDepartment.Focus()
61 If (rdr IsNot Nothing) Then
62 rdr.Close()
63 End If
64 Return
65 End If
66
67 con = New SqlConnection(cs)
68 con.Open()
69
70 Dim cb As String = "insert into Department(ID,DepartmentName) VALUES (" & txtID.Text & ",@d1)"
71 cmd = New SqlCommand(cb)
72 cmd.Parameters.AddWithValue("@d1", txtDepartment.Text)
73 cmd.Connection = con
74 cmd.ExecuteReader()
75 con.Close()
76 Dim st As String = "added the new Department '" & txtDepartment.Text & "'"
77 LogFunc(lblUser.Text, st)
78 MessageBox.Show("Successfully Saved", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
79 btnSave.Enabled = False
80 Getdata()
81 Catch ex As Exception
82 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
83 End Try
84 End Sub
85
86 Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
87 If txtDepartment.Text = "" Then
88 MessageBox.Show("Please enter Department", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
89 txtDepartment.Focus()
90 Return
91 End If
92
93 Try
94
95 con = New SqlConnection(cs)
96 con.Open()
97
98 Dim cb As String = "Update Department set DepartmentName=@d1 where ID=@d2"
99 cmd = New SqlCommand(cb)
100 cmd.Connection = con
101 cmd.Parameters.AddWithValue("@d1", txtDepartment.Text)
102 cmd.Parameters.AddWithValue("@d2", txtID.Text)
103 cmd.ExecuteReader()
104 con.Close()
105 Dim st As String = "updated the Department '" & txtDepartment.Text & "'"
106 LogFunc(lblUser.Text, st)
107 MessageBox.Show("Successfully updated", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
108 btnUpdate.Enabled = False
109 Getdata()
110 Catch ex As Exception
111 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
112 End Try
113 End Sub
114 Private Sub DeleteRecord()
115
116 Try
117 Dim RowsAffected As Integer = 0
118 con = New SqlConnection(cs)
119 con.Open()
120 Dim cl As String = "select ID from Staff_Department,Department where Staff_Department.DepartmentID=Department.ID and ID=@d1"
121 cmd = New SqlCommand(cl)
122 cmd.Connection = con
123 cmd.Parameters.AddWithValue("@d1", txtID.Text)
124 rdr = cmd.ExecuteReader()
125 If rdr.Read Then
126 MessageBox.Show("Unable to delete..Already in use in Staff Entry", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
127 If Not rdr Is Nothing Then
128 rdr.Close()
129 End If
130 Exit Sub
131 End If
132 con = New SqlConnection(cs)
133 con.Open()
134 Dim cq As String = "delete from Department where ID=@d1"
135 cmd = New SqlCommand(cq)
136 cmd.Parameters.AddWithValue("@d1", txtID.Text)
137 cmd.Connection = con
138 RowsAffected = cmd.ExecuteNonQuery()
139 If RowsAffected > 0 Then
140 Dim st As String = "deleted the Department '" & txtDepartment.Text & "'"
141 LogFunc(lblUser.Text, st)
142 MessageBox.Show("Successfully deleted", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
143 Getdata()
144 Reset()
145 Else
146 MessageBox.Show("No Record found", "Sorry", MessageBoxButtons.OK, MessageBoxIcon.Information)
147 Reset()
148 End If
149 If con.State = ConnectionState.Open Then
150 con.Close()
151
152 End If
153 Catch ex As Exception
154 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
155 End Try
156 End Sub
157 Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
158 Try
159 If MessageBox.Show("Do you really want to delete this record?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = Windows.Forms.DialogResult.Yes Then
160 DeleteRecord()
161 End If
162 Catch ex As Exception
163 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
164 End Try
165 End Sub
166
167 Private Sub dgw_MouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles dgw.MouseClick
168 Try
169 Dim dr As DataGridViewRow = dgw.SelectedRows(0)
170 txtID.Text = dr.Cells(0).Value.ToString()
171 txtDepartment.Text = dr.Cells(1).Value.ToString()
172 btnUpdate.Enabled = True
173 btnDelete.Enabled = True
174 btnSave.Enabled = False
175 Catch ex As Exception
176 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
177 End Try
178 End Sub
179
180 Private Sub dgw_RowPostPaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
181 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
182 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
183 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
184 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
185 End If
186 Dim b As Brush = SystemBrushes.ControlText
187 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
188
189 End Sub
190 Public Sub Getdata()
191 Try
192 con = New SqlConnection(cs)
193 con.Open()
194 cmd = New SqlCommand("SELECT RTRIM(ID),RTRIM(DepartmentName) from Department order by DepartmentName", con)
195 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
196 dgw.Rows.Clear()
197 While (rdr.Read() = True)
198 dgw.Rows.Add(rdr(0), rdr(1))
199 End While
200 con.Close()
201 Catch ex As Exception
202 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
203 End Try
204 End Sub
205
206 Private Sub frmtype_Load_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
207 Getdata()
208 End Sub
209 End Class
